package com.urban.pooling.repository;

import java.util.Date;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.urban.pooling.domain.Route;

public interface RouteRepository extends CrudRepository<Route, Integer> {

	/*
	 * @Query(value=
	 * "SELECT r.* FROM routes r WHERE r.start_point IN( SELECT p.point_id FROM geo_points"
	 * +
	 * " p WHERE p.point_id = r.start_point AND geo_dist(p.lat, p.lng, ?1, ?2) < ?8) AND"
	 * +
	 * " r.dest_point IN( SELECT p.point_id FROM geo_points p WHERE p.point_id = r.dest_point AND"
	 * +
	 * " geo_dist(p.lat, p.lng, ?3, ?4) < ?8) AND r.start_time >= ?5 AND r.start_time<=?6 AND r.seats>=?7 "
	 * , nativeQuery = true)
	 */
	@Query(value = "SELECT r.route_id, r.car, r.seats,r.start_address, r.dest_address, r.start_time, r.arrival_time"
			+ ",r.driver, r.last_modified,r.every_work_day FROM routes r WHERE "
			+ "(SELECT p.point_order FROM geo_points p WHERE p.route_id = r.route_id AND geo_dist(p.lat, p.lng, ?1, ?2) < ?8 "
			+ "ORDER BY p.point_order ASC LIMIT 0,1 ) < (SELECT p.point_order FROM geo_points p"
			+ " WHERE p.route_id = r.route_id AND geo_dist(p.lat, p.lng, ?3, ?4) < ?8 ORDER BY p.point_order DESC LIMIT 0,1 )"
			+ " AND r.start_time >= ?5 AND r.start_time<=?6 AND r.seats>=?7 ", nativeQuery = true)
	public Iterable<Route> searchRoutes(double startLat, double startLng, double destLat, double destLng,
			Date startDateTime, Date upperStartDate, int availableSeats, float radius);

	public Iterable<Route> findByStartTimeGreaterThanOrderByLastModifiedDesc(Date startDate);

	@Query(value = "FROM Route r WHERE r.driver.username=?1")
	public Iterable<Route> findByUsername(String username);
	
	@Query("SELECT coalesce(sum(rr.seats),0) FROM RouteRequest rr WHERE rr.routeRequestStatus.rrsId =2 AND rr.route.routeId = ?1")
	public Long getOccupiedSeats(int routeId);
}
